﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
   public class BoundDAL
    {
        SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
        /// <summary>
        /// 加入
        /// </summary>
        /// <returns></returns>
        public List<Bound> ShowBound()
        {
            str.Open();
            string sql = "select * from Bound";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Bound> listBound = new List<Bound>();
            foreach (DataRow item in ta.Rows)
            {
                Bound b = new Bound();
                b.BoundCode = item["BoundCode"].ToString();
                b.BoundContent = item["BoundContent"].ToString();
                b.BoundId = Convert.ToInt32(item["BoundId"]);
                b.BoundName = item["BoundName"].ToString();
                b.BoundPhone = item["BoundPhone"].ToString();
                listBound.Add(b);
            }
            return listBound;

        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteBound(int id)
        {
            str.Open();
            string sql = "delete Bound where BoundId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int Bound_Add(Model.Bound p)
        {
            str.Open();
            string sql = "insert into Bound values('" + p.BoundCode + "','" + p.BoundContent + "','"+p.BoundName+"','"+p.BoundPhone+"')";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 显示详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Bound ShowBoundX(int id)
        {
            str.Open();
            string sql = "select * from Bound where BoundId='" + id + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Bound> listBound = new List<Bound>();
            foreach (DataRow item in ta.Rows)
            {
                Bound b = new Bound();
                b.BoundCode = item["BoundCode"].ToString();
                b.BoundContent = item["BoundContent"].ToString();
                b.BoundId = Convert.ToInt32(item["BoundId"]);
                b.BoundName = item["BoundName"].ToString();
                b.BoundPhone = item["BoundPhone"].ToString();
                listBound.Add(b);
            }
            return listBound.FirstOrDefault();
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int UpdateBound(Bound data)
        {
            str.Open();
            string sql = "update Project set  BoundCode='" + data.BoundCode + "',BoundContent='" + data.BoundContent + "',BoundName='" + data.BoundName + "',BoundPhone='" + data.BoundPhone + "' where BoundId='" + data.BoundId + "'";
            SqlCommand com = new SqlCommand(sql, str);
            int i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        public List<Bound> SeleteBound(string name)
        {
            str.Open();
            string sql = "selete BoundCode,BoundName,BoundPhone,BoundId,BoundContent from Project where BoundName like '" + name + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            List<Bound> listBound = new List<Bound>();
            foreach (DataRow item in ta.Rows)
            {
                Bound b = new Bound();
                b.BoundCode = item["BoundCode"].ToString();
                b.BoundContent = item["BoundContent"].ToString();
                b.BoundId = Convert.ToInt32(item["BoundId"]);
                b.BoundName = item["BoundName"].ToString();
                b.BoundPhone = item["BoundPhone"].ToString();
                listBound.Add(b);
            }
            return listBound;
        }
    }
}
